package com.ph.irp.base.log.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.log.dao.PBaseLogDAO;
import com.ph.irp.model.PBaseLog;
import com.ph.util.DBUtil;

public class PBaseLogDAOImpl implements PBaseLogDAO {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List<PBaseLog> getAllLog() throws Throwable {
		List<PBaseLog> resList = new ArrayList<PBaseLog>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select l.state stat,l.happenTime hdate,");
			sb.append("l.LogInfo loginfo,l.userid id,");
			sb.append("l.FunctionID functionid ");
			sb.append("from p_base_log l");
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseLog log = new PBaseLog();

				log.setState(rs.getInt("stat"));
				log.setHappenTime(rs.getTimestamp("hdate"));
				log.setLogInfo(rs.getString("loginfo"));
				log.setUserId(rs.getInt("id"));
				log.setFunctionId(rs.getInt("functionid"));

				resList.add(log);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	public List<PBaseLog> getLogByUserNameAndTime(int userId, String timeFrom,
			String timeTo) throws Throwable {

		List<PBaseLog> resList = new ArrayList<PBaseLog>();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();

			sb.append("select l.state stat,l.happenTime hdate,");
			sb.append("l.LogInfo loginfo,l.userid id,");
			sb.append("l.FunctionID functionid ");
			sb.append("from p_base_log l ");
			sb.append("where 1=1 ");
			if (userId != 0) {
				sb.append("and l.userid=").append(userId);
				if (timeFrom != null && !timeFrom.equals("")) {
					sb.append(" and ");
					sb.append("l.happenTime>='").append(timeFrom).append("'");
				}

				if (timeTo != null && !timeTo.equals("")) {
					sb.append(" and ");
					sb.append("l.happenTime<='").append(timeTo).append("'");
				}
			} else {
				if (timeFrom != null && !timeFrom.equals("")) {
					sb.append("and l.happenTime>='").append(timeFrom).append(
							"'");
					if (timeTo != null && !timeTo.equals("")) {
						sb.append(" and ");
						sb.append("l.happenTime<='").append(timeTo).append("'");
					}
				} else {
					if (timeTo != null && !timeTo.equals("")) {
						sb.append("and l.happenTime<='").append(timeTo).append(
								"'");
					}
				}
			}

			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();

			while (rs.next()) {
				PBaseLog log = new PBaseLog();

				log.setState(rs.getInt("stat"));
				log.setHappenTime(rs.getTimestamp("hdate"));
				log.setLogInfo(rs.getString("loginfo"));
				log.setUserId(rs.getInt("id"));
				log.setFunctionId(rs.getInt("functionid"));

				resList.add(log);

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;

	}

	public void deleteAllLog() throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sb = new StringBuilder();
			sb.append(" delete from p_base_log ");

			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	public void addLog(PBaseLog log) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append(" insert into p_base_log(UserID,FunctionID,State,LogInfo,happenTime)");
		sb.append(" values(");
		sb.append(log.getUserId());
		sb.append(",").append(log.getFunctionId());
		sb.append(",").append(log.getState());
		sb.append(",'").append(log.getLogInfo());
		sb.append("','").append(log.getHappenTime());
		sb.append("')");

		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}
}
